package com.example.demo.service.impl;

import com.example.demo.constant.BaseConstant;
import com.example.demo.module.DTO.Datasource;
import com.example.demo.module.DTO.DatasourceDto;
import com.example.demo.module.DTO.Metadata;
import com.example.demo.module.DTO.OriginalTable;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author MarioZzz
 * @create 2023-08-02 18:04
 */
@Service
@Slf4j
public class DataSourceService {

    public Map<String, Object> pullDataSource(String jsonString, String type) throws SQLException {
        Map<String, Object> resMap = new HashMap<>(4);
        Datasource datasource = new Datasource();
        datasource.setDatasourceJson(jsonString);
        datasource.setTypeDetil(type);
        DatasourceDto datasourceDto = datasource.toDatasourceDto();
        // 获取连接
        Connection connect = connectJDBC(datasourceDto);
        if (connect == null) {
            log.error("connect is null");
            return resMap;
        } else {
            ResultSet tables = null;
            try {
                // 数据库元数据信息
                DatabaseMetaData databaseMetaData = connect.getMetaData();
                Long dsId = datasourceDto.getDsId();

                // 获取表拉取规则，前缀黑白名单，后缀黑白名单，正则表达式
                String dsPrefixWhite = datasourceDto.getPrefixWhite();
                String dsPrefixBlack = datasourceDto.getPrefixBlack();
                String dsSuffixWhite = datasourceDto.getSuffixWhite();
                String dsSuffixBlack = datasourceDto.getSuffixBlack();
                String dsRegularFilter = datasourceDto.getRegularFilter();
                // 表白名单，前后缀白名单同时存在时，暂定且关系
                String nameWhite = (StringUtils.isNotBlank(dsPrefixWhite) ? dsPrefixWhite : "") + "%" + (StringUtils.isNotBlank(dsSuffixWhite) ? dsSuffixWhite : "");
                // 表黑名单，前后缀黑名单同时存在时，暂定且关系
                String nameBlack = null;
                if (StringUtils.isNotBlank(dsPrefixBlack) || StringUtils.isNotBlank(dsSuffixBlack)) {
                    nameBlack = (StringUtils.isNotBlank(dsPrefixBlack) ? dsPrefixBlack : "") + ".*" + (StringUtils.isNotBlank(dsSuffixBlack) ? dsSuffixBlack : "");
                }
                /*
                 *  获取符合白名单的表信息
                 * */
                String catalog = connect.getCatalog();
                String schema = connect.getSchema();
                System.out.println("schema名称================" + schema);
                if (BaseConstant.ORACLE.equals(datasourceDto.getTypeDetil())) {
                    Pattern compile = Pattern.compile("^jdbc:oracle:thin:@((((25[0-5]|2[0-4]\\d|[0-1]\\d{2}|[1-9]?\\d)\\.){3}(25[0-5]|2[0-4]\\d|[0-1]\\d{2}|[1-9]?\\d))|([A-Za-z0-9_.]+)):(([1-5]?([0-9]{0,4}))|(6[0-5][0-5][0-3][0-5])):([A-Za-z0-9_]+)$");
                    Matcher matcher = compile.matcher(datasourceDto.getUrl());
                    if (matcher.find()) {
                        schema = matcher.group(matcher.groupCount()).toUpperCase();
                    }
                }
                // mysql:catalog、schema二选一，看数据库类型，一般是catalog
                // postgresql:catalog 不影响
                // 达梦:catalog 不影响
                // oracle大写:catalog 不影响
                tables = databaseMetaData.getTables(catalog, schema, nameWhite, new String[]{"TABLE"});
                List<OriginalTable> originalTables = new ArrayList<>();
                while (tables.next()) {
                    // 表名，英文名
                    String tableName = tables.getString("TABLE_NAME");
                    if (nameBlack != null && tableName.matches(nameBlack)) {
                        // 存在黑名单，符合黑名单跳过
                        continue;
                    }
                    if (StringUtils.isNotBlank(dsRegularFilter) && !tableName.matches(dsRegularFilter)) {
                        // 存在正则过滤，且表名不符合正则就跳过
                        continue;
                    }
                    // 注释，中文名
                    String desc = tables.getString("REMARKS");

                    OriginalTable originalTable = new OriginalTable();
                    originalTable.setDsId(dsId);
                    originalTable.setOtEnName(tableName);
                    if (StringUtils.isNotBlank(desc)) {
                        originalTable.setOtChName(StringUtils.substring(desc, 0, 40));
                        originalTable.setTheDesc(StringUtils.substring(desc, 0, 500));
                    } else {
                        originalTable.setOtChName("表" + tableName);
                        originalTable.setTheDesc("表" + tableName + "的信息");
                    }
                    // 获取表对应的列元数据
                    List<Metadata> metadatas = getMetadatas(databaseMetaData, catalog, schema, tableName);
                    originalTable.setMetadatas(metadatas);
                    originalTables.add(originalTable);
                }
                // 保存所有的表元数据
                originalTables.forEach(System.out::println);
                resMap.put("originalTables", originalTables);
                List<Metadata> allMetadatas = new ArrayList<>();
                originalTables.forEach(originalTable -> {
                    originalTable.getMetadatas().forEach(metadata -> {
                        metadata.setDsId(dsId);
                        metadata.setOtId(originalTable.getOtId());
                    });
                    allMetadatas.addAll(originalTable.getMetadatas());
                });
                // 保存所有的列元数据
                resMap.put("allMetaData", allMetadatas);
                allMetadatas.forEach(System.out::println);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                // 关闭资源
                connect.close();
            }
        }
        return resMap;
    }

    private static Connection connectJDBC(DatasourceDto datasourceDto) {
        Connection connection = null;
        Properties properties = new Properties();
        properties.setProperty("user", datasourceDto.getUsername());
        properties.setProperty("password", datasourceDto.getPassword());
        // 解决mysql获取表注释失败
        properties.setProperty("useInformationSchema", "true");
        String driver = datasourceDto.getDriver();
        //1.加载驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        //2.连接数据库
        try {
            //连接
            connection = DriverManager.getConnection(datasourceDto.getUrl(), properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static List<Metadata> getMetadatas(DatabaseMetaData databaseMetaData, String catalog, String schema, String tableName) {
        // 字段信息
        List<Metadata> metadatas = new ArrayList<>();
        ResultSet primaryKeys = null;
        ResultSet uniqueInfos = null;
        ResultSet columns = null;
        try {
            // 获取主键字段列表，不考虑联合主键
            primaryKeys = databaseMetaData.getPrimaryKeys(catalog, schema, tableName);
            Set<String> keyColumns = new HashSet<>();
            while (primaryKeys.next()) {
                if (keyColumns.size() == 1) {
                    // 联合主键，直接退出，keyColumns设置null
                    keyColumns.clear();
                    break;
                }
                keyColumns.add(primaryKeys.getString("COLUMN_NAME"));
            }
            // 获取唯一约束,不考虑多字段唯一
            // approximate近似值，指获取的索引信息是否一定要准确：mysql、pg、dm无效；
            // oracle分析表获取准确表信息，有效，若获取准确值，当表名小写时，会无法找到表，需要添加引号 “表名”。
            uniqueInfos = databaseMetaData.getIndexInfo(catalog, schema,
                    tableName, true, true);
            Map<String, List<String>> uniques = new HashMap<>();
            while (uniqueInfos.next()) {
                String indexName = uniqueInfos.getString("INDEX_NAME");
                List<String> list = uniques.computeIfAbsent(indexName, k -> new ArrayList<>());
                list.add(uniqueInfos.getString("COLUMN_NAME"));
            }
            Set<String> indexNames = new HashSet<>(uniques.keySet());
            for (String indexName : indexNames) {
                // 删除多字段约束
                if (uniques.get(indexName).size() > 1) {
                    uniques.remove(indexName);
                }
            }
            columns = databaseMetaData.getColumns(catalog, schema, tableName, null);
            while (columns.next()) {
                Metadata metadata = new Metadata();
                //列名,英文名
                String colName = columns.getString("COLUMN_NAME");
                metadata.setEnColname(colName);
                //列描述，中文名
                String desc = columns.getString("REMARKS");
                if (StringUtils.isNotBlank(desc)) {
                    metadata.setChColname(StringUtils.substring(desc, 0, 60));
                    metadata.setTheDesc(StringUtils.substring(desc, 0, 500));
                } else {
                    metadata.setChColname("列" + colName);
                    metadata.setTheDesc("列" + colName + "的信息");
                }
                //java.sql.Types类型名称(列类型名称)，数据类型
                metadata.setColDatatype(columns.getString("TYPE_NAME"));
                //列大小，数据长度
                metadata.setMdLength(columns.getInt("COLUMN_SIZE"));
                //精度
                metadata.setMdAccuracy(columns.getInt("DECIMAL_DIGITS"));

                metadata.setTableName(tableName);
                // 是否主键
                boolean mdPrimary = keyColumns.contains(colName);
                metadata.setMdPrimary(mdPrimary ? 1 : 0);
            /*
              是否允许为null
               0 (columnNoNulls) - 该列不允许为空
               1 (columnNullable) - 该列允许为空
               2 (columnNullableUnknown) - 不确定该列是否为空
             */
                metadata.setMdNull(columns.getInt("NULLABLE"));
                //是否唯一,是主键或存在唯一约束
                boolean mdUnique = Boolean.FALSE;
                for (List<String> list : uniques.values()) {
                    mdUnique = list.contains(colName) || mdUnique;
                }
                metadata.setMdUnique(mdUnique || mdPrimary ? 1 : 0);
                //是否加密？
                metadata.setMdEncry(0);
                //加密类型？
                metadatas.add(metadata);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        return metadatas;
    }
}
